import datetime

import sqlalchemy
from sqlalchemy import create_engine
from urllib.parse import quote
from sqlalchemy.orm import declarative_base, Session
from sqlalchemy import Column, DateTime, String, insert, Integer

"""
create database learn_db;
use learn_db;
create table staff
(
    id          int auto_increment
        primary key,
    create_time datetime     null,
    name       varchar(255) not null,
    address       varchar(255) not null
);


"""

print(sqlalchemy.__version__)

"""
https://docs.sqlalchemy.org/en/14/tutorial/engine.html
future=True代表使用2.0风格的连接 在2.0
`with engine.connect() as conn` 代码将不会自动commit， 并且会在代码块中执行ROLLBACK
 `with Session(engine) as session` 在2.0同样不会自动提交 并且会在代码块中执行ROLLBACK(如果没有commit)
"""
engine = create_engine(f'mysql://root:{quote("kexin@kexin2021")}@192.168.1.80/learn_db', echo=True, future=True)

engine.connect()

Base = declarative_base(engine)


# see https://docs.sqlalchemy.org/en/14/tutorial/metadata.html#declaring-mapped-classes

class Staff(Base):
    __tablename__ = 'staff'
    id = Column(Integer, primary_key=True)
    create_time = Column(DateTime)
    name = Column(String(255))
    address = Column(String(255))


# https://docs.sqlalchemy.org/en/14/tutorial/metadata.html#emitting-ddl-to-the-database
# https://docs.sqlalchemy.org/en/14/tutorial/metadata.html#id1
Base.metadata.create_all()

with engine.connect() as conn:
    # https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#committing-changes
    stmt = insert(Staff).values(name='baloneo', address='北京', create_time=datetime.datetime.now())
    result = conn.execute(stmt)
    print(stmt, result)


with Session(engine) as session:
    # https://docs.sqlalchemy.org/en/14/tutorial/dbapi_transactions.html#executing-with-an-orm-session
    stmt = insert(Staff).values(name='baloneo2', address='北京', create_time=datetime.datetime.now())
    result = session.execute(stmt)
    print(result)
    session.commit()

# with Session(engine) as session:
#     stmt = insert(Staff).values(name='baloneo3', address='北京', create_time=datetime.datetime.now())
#     result = session.execute(stmt)
#     print(result)
#     raise Exception('出现异常会自动rollback')
#     session.commit()

print('*' * 10)
# 使用ORM插入
with Session(engine) as session:
    print('baloneo4 inserting...')
    obj: Staff = Staff()
    obj.address = '北京2'
    obj.name = 'baloneo4'
    obj.create_time = datetime.datetime.now()
    session.add(obj)
    session.commit()
    # session.flush()  https://docs.sqlalchemy.org/en/14/tutorial/orm_data_manipulation.html#flushing 没有必要flush
    print('baloneo4 over...')

with engine.connect() as conn:
    from sqlalchemy.sql import text
    result = conn.execute(text("SHOW VARIABLES WHERE Variable_name='autocommit';"))
    print(result, 'sdsd')
    for i in result:
        print(i)